<?php
/**
 * 数据库生成200万条记录的php文件。
 * 请确认php安装了PDO扩展，并使用命令行运行。
 * 运行后，province_id为1的数据会相对多一点，这是故意的。
 * 
 * 问题：
 * 存在两个前台必须有的需求（不同的地方在于排序方式），分别为：
 * （1）分页显示某省份、某种商店类型、处于启用状态的商店，并且按推荐排序倒排序。SQL语句形如：
 * SELECT SQL_NO_CACHE `shop_id` FROM `shop_metadata` WHERE `province_id` = '1' AND `type` = '1' AND `state` = '0' ORDER BY `sort` DESC LIMIT 0, 30;
 * （2）分页显示某省份、某种商店类型、处于启用状态的商店，并且按商店拥有的货物数目倒排序。SQL语句形如：
 * SELECT SQL_NO_CACHE `shop_id` FROM `shop_metadata` WHERE `province_id` = '1' AND `type` = '1' AND `state` = '0' ORDER BY `item_count` DESC LIMIT 0, 30;
 * 
 * 现在的问题有：
 * （1）为什么查询时，会出现“Using filesort”，并且mysql需要创建临时文件（Created_tmp_files）？
 * （2）应该创建怎样的索引才是最好的？
 * 
 * @version $Id: pdo_insert.php 215 2012-05-15 10:38:21Z horseluke@126.com $
 */

/*
 * 数据库DDL：
CREATE TABLE `shop_metadata` (
  `shop_id` int(11) unsigned NOT NULL auto_increment COMMENT '商店id',
  `province_id` int(11) unsigned NOT NULL default '0' COMMENT '所属省份id，1到35',
  `type` int(11) NOT NULL default '1' COMMENT '商店类型。1：连锁店；2：个体户；3：shopping mall',
  `state` tinyint(2) unsigned NOT NULL default '0' COMMENT '审核和启用状态，0：正常；1：已经关闭',
  `item_count` int(11) unsigned NOT NULL default '0' COMMENT '商店拥有的货物数目',
  `sort` int(11) unsigned NOT NULL default '0' COMMENT '推荐排序',
  PRIMARY KEY  (`shop_id`),
  KEY `province_type_state` (`province_id`,`type`,`state`),
  KEY `sort` (`sort`),
  KEY `item_count` (`item_count`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 */


//配置区
define('DB_PORT',		'3306');
define('DB_HOST',		'localhost');
define('DB_USER',		'root');
define('DB_PASSWD',		'cat1');
define('DB_CHARSET',	'utf8');
define('DB_NAME',		'test');



//代码区
class pdomysqlConnector{
    
    protected static $_isEqualOrAbove5_3_6 = null;
    
	/**
	 * @static
	 * PDO在5.3.6之前会忽略charset设置{@link http://www.php.net/manual/zh/ref.pdo-mysql.connection.php},用户仍需要SQL设置？
	 * @param string|null $setcharset 不传值表示不进行SQL字符集设置。若需传值，其可选值有：
	 * VUL：表示采用有漏洞的SET NAMES进行字符集设置，诱发漏洞；
	 * BIN：以较为稳妥的方式进行字符集设置
	 * 
	 */
	static public function connect(){
	    $charset = DB_CHARSET ? strtolower(DB_CHARSET) : 'utf8';
		$dsn = "mysql:dbname=".DB_NAME.";host=".DB_HOST.";port=".DB_PORT;
		
		if(self::isEqualOrAbove5_3_6()){
		    //http://www.php.net/manual/zh/ref.pdo-mysql.connection.php
		    $dsn .= ';charset='. $charset;
		}
		
		$pdoInstance = new PDO($dsn, DB_USER, DB_PASSWD);
		$pdoInstance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		self::_setCharset($charset, $pdoInstance);
		
		return $pdoInstance;
	}
	
	/**
	 * 设置db charset
	 */
	static protected function _setCharset($charset, PDO $pdoInstance){
		$version = $pdoInstance->getAttribute(PDO::ATTR_SERVER_VERSION);
		$charset = strtolower($charset);
		if($version > '4.1'){
			$sql = $charset ? "character_set_connection={$charset}, character_set_results={$charset}, character_set_client=binary" : '';
			$sql .= $version > '5.0.1' ? ((empty($sql) ? '' : ', ')."sql_mode=''") : '';
			if($sql){
			    $pdoInstance->exec("SET {$sql}");
			}
		}
	}
	
	static protected function isEqualOrAbove5_3_6(){
	    if(null === self::$_isEqualOrAbove5_3_6){
	        self::$_isEqualOrAbove5_3_6 = version_compare(PHP_VERSION, '5.3.6', '>=');
	    }
	    return self::$_isEqualOrAbove5_3_6;
	}
	
}

$pdo = pdomysqlConnector::connect();

$sth = $pdo->prepare('INSERT INTO `shop_metadata` (`province_id`, `type`, `state`, `item_count`, `sort`) VALUES (:province_id, :type, :state, :item_count, :sort)');

for($k = 1;$k <= 2000000;$k++){
	//23个省，5个自治区，4个直辖市，2个特别行政区
	$sth->execute(
		array(':province_id' => (mt_rand(1,100) <= 7 ? 1 : mt_rand(2, 35)),
		':type'=>mt_rand(1,3), 
		':state'=>(mt_rand(1,100) <= 90 ? 0 : 1), 
		':item_count'=>mt_rand(0, 100), 
		':sort'=>(mt_rand(1,100) <= 70 ? 0 : mt_rand(1, 100)),
		)
	);
	echo "[{$k}]finish insert\r\n";
	if($k % 20000 == 0){
		$sleep_s = mt_rand(1,5);
		echo ">>>[pause]sleep {$sleep_s}s\r\n";
		sleep($sleep_s);
	}
}


echo 'all finish.';